/*
 * transDAO.java
 *
 * Created on March 17, 2007, 2:14 PM
 *
 * To change this ttrnslate, choose Tools | Ttrnslate Manager
 * and open the ttrnslate in the editor.
 */

package edu.byu.isys413.mystuff;

import java.util.*;
import java.sql.*;

/**
 *
 * @author Sandi Griffiths
 */
public class transDAO {
    
    private static transDAO instance;
    private String transid;
    private static Cache cache;
    
    /** Creates a new instance of transDAO */
    public transDAO() {
    cache = Cache.getInstance();
    }
        
    public static synchronized transDAO getInstance() {
        if (instance == null) {
            instance = new transDAO();
            
        }
        return instance;
    }
    
    public transBO create(){
        transid = Guid.generate();
        transBO trns = new transBO(transid);
        cache.put(transid, trns);
        return trns;
    }
    
    /*
     *This method reads the record from the database  and loads the Customer Business object
     * @parameter : the id of the record
     * @ returns : the customerBO, loaded with the data from the database
     */
    public synchronized ArrayList<transBO> readALL() throws DataException {
        ArrayList<transBO> trnslist = new ArrayList();
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            System.out.println("Check");
            Statement st = conn.createStatement();
            System.out.println("Executing Query");
            ResultSet rs = st.executeQuery("SELECT * FROM TRANSAC_TABLE");
            System.out.println("Execute Succesful");
            
            while(rs.next()){
                transBO trns = new transBO(rs.getString("TRANSACID"));
                trns.setPaymentId(rs.getString("PAY_ID"));//artist
                trns.setStoreId(rs.getString("STORE_ID"));//album
                trns.setEmployeeId(rs.getString("EMP_ID"));
                trns.setCustId(rs.getString("CUST_ID"));
                if(rs.getString("COMPLETE").equals("true"))
                    trns.setComplete(true);
                else
                    trns.setComplete(false);
                trns.setTotal(Double.valueOf(rs.getString("PRICE").trim()).doubleValue());
                trns.setDate(rs.getString("DATE"));
                trns.setAlreadyInDatabase(true);
                trnslist.add(trns);
            }
            System.out.println("Not this far?");
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return trnslist;
    }
    
    public synchronized transBO read(String transid) throws DataException{
        transBO str = null;
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            if(cache.containsKey(transid))
                str = (transBO)cache.get(transid);
            else
                str = read(conn, transid);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return str;
    }
    
    public synchronized transBO read(Connection conn, String id){
       transBO trns = null;
        try{
        Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM TRANSAC_TABLE WHERE TRANSACID='"+id+"'");
            trns = new transBO(id);
            while(rs.next()){
                trns.setPaymentId(rs.getString("PAY_ID"));//artist
                trns.setStoreId(rs.getString("STORE_ID"));//album
                trns.setEmployeeId(rs.getString("EMP_ID"));
                trns.setCustId(rs.getString("CUST_ID"));
                if(rs.getString("COMPLETE").equals("true"))
                    trns.setComplete(true);
                else
                    trns.setComplete(false);
                trns.setTotal(Double.valueOf(rs.getString("PRICE").trim()).doubleValue());
                trns.setDate(rs.getString("DATE"));
                trns.setAlreadyInDatabase(true);
            }
       }catch(Exception e){}
       cache.put(id, trns);
       return trns;
    }
    ///////////////update//////////////////
    
    /////////////save////////////////////
    public synchronized boolean save(transBO bo) throws DataException {
       System.out.println("Save entrance accessed");
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            if(bo.isDirty())
                save(conn,bo);
            bo.setDirty(false);
            
            if(bo.getTransLineList()!=null){
                tlineDAO tld = tlineDAO.getInstance();
                for(int x=0; x<bo.getTransLineList().size(); x++)
                    tld.save(conn, (tlineBO)bo.getTransLineList().get(x));
            }
            if(bo.getPayment() != null){
                paymentDAO pd = paymentDAO.getInstance();
                pd.save(conn, bo.getPayment());
            }
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
        }
    
    public synchronized void save(Connection conn, transBO bo){
        System.out.println("Second Save Accessed");
        try{
            Statement st = conn.createStatement();
            String transid = "";
           
            System.out.println("First Query Skipped");
            if(false == bo.isAlreadyInDatabase()){
                
                String queryString = "INSERT INTO TRANSAC_TABLE VALUES (?,?,?,?,?,?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
               
                //String aString = Double.toString(aDouble);
                pstmt.setString(1,bo.getTransactionId());
                pstmt.setString(2,bo.getPaymentId());
                pstmt.setString(3,bo.getStoreId());
                pstmt.setString(4,bo.getEmpId());
                pstmt.setString(5,bo.getCustId());
                pstmt.setString(6,bo.getComplete()+"");
                pstmt.setString(7,Double.toString(bo.getTotal()));
                pstmt.setString(8,bo.getDate());
                pstmt.execute();
                pstmt.close();
                bo.setAlreadyInDatabase(true);
            } else{
            
                String queryString = "UPDATE TRANSAC_TABLE SET PAY_ID = ?, STORE_ID = ?, EMP_ID = ?, CUST_ID = ?, COMPLETE = ?, PRICE = ?, DATE = ? WHERE TRANSACID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,bo.getPaymentId());
                pstmt.setString(2,bo.getStoreId());
                pstmt.setString(3,bo.getEmpId());
                pstmt.setString(4,bo.getCustId());
                pstmt.setString(5,bo.getComplete()+"");
                pstmt.setString(6,Double.toString(bo.getTotal()));
                pstmt.setString(7,bo.getDate());
                pstmt.setString(8,bo.getTransactionId());
                pstmt.execute();
                pstmt.close();
                
            }
        }catch(Exception e){}
    }
    
    /////////////delete////////////////////////
    /*
        We're assuming we will never need to delete a store, if a faulty one is made we can just set it update it 
     *  to an actual new store. No problems, We shouldn't be deleting stores, anyways.
     */
    
}
